Oracle数据库表备份数据泵导入导出详解

        

                             ORACLE数据导入导出

一、导入导出方式

1.普通导入导出

• imp导入

  imp 用户名/密码@数据库IP:端口号/orcl FILE=导入的目标文件名称 TABLES=表名(多个表时用逗号隔开)

  imp TJNS_DS/bangsun@10.10.1.54:1521/orcl FILE=exp.dmp TABLES=（USER_INFO,POLICY_INFO）;

• exp导出

  exp 用户名/密码@数据库IP:端口号/orcl FILE=导出的目标文件名称 TABLES=表名(多个表时用逗号隔开)

  exp TJNS_DS/bangsun@10.10.1.54:1521/orcl FILE=exp.dmp TABLES=（USER_INFO,POLICY_INFO）;

• 命令详解

  file --导出文件路径及文件名，文件后缀为.dmp
  tables --要导出的表名，导出多个表时表名要写在括号内并用逗号隔开
  ignore --赋值为y时，如果表已经存在，会跳过，否则将会报错
  owner  --导出时使用，用于导出所属用户的全部内容
  full   --赋值为y时，导出完整的数据库
  fromuser --导入时使用，指源数据库用户名
  touser   --导入时使用，值要导入的数据库用户名
  

2.数据泵导入导出

• impdp 导入

​ impdp 用户名/密码@数据库IP:端口号/orcl DUMPFILE=导出的目标文件名称 TABLES=表名(多个表时逗号隔开)

​ impdp TJNS_DS/pass@10.10.1.54:1521/orcl DUMPFILE=export.dmp TABLES=USER_INFO,POLICY_INFO

• expdp导出

​ expdp 用户名/密码@数据库IP:端口号/orcl DUMPFILE=导出的目标文件名 TABLES=表名(多个表时用逗号隔开)

​ expdp TJNS_DS/pass@10.10.1.54:1521/orcl DUMPFILE=export.dmp TABLES=USER_INFO,POLICY_INFO;

• 命令详解

directory --导出逻辑目录，该名称指向字典DBA_DIRECTORIES中该名称对应的路径
dumpfile --导出文件的名称，后缀为.dmp
logfile --记录导出过程日志，后缀为.log
tables|schemas|tablespaces|full --分别表示导出表、模式（用户）、表空间、全库
remap_schema=from_schema:to_schema --改变用户所属,从哪个用户来，要到哪个用户去
remap_tablespace=from_tbs:to_tbs --改变表空间
table_exists_action={skip|append|replace|truncate} --表存在时的处理：跳过、补充、替换、清空
SKIP:当表存在时，什么操作都不做，直接跳过
APPEND:当表存在时，在现有数据的基础之上补充数据
REPLACE:当表存在时，替换表结构和数据，相当于删除表后又重建插数据
TRUNCATE:当表存在时，清空表，再插入数据

3.两种方式的区别

• exp和imp可用于所有的版本，而数据泵方式只能用于10g以及之后的版本

• 速度方面，数据泵方式要比传统的imp要快很多

• 指定导出文件路径方面，传统方式导出可以指定任意有权限的目录，而数据泵导方式必须使用逻辑目录

• 导入时如果要覆盖已存在的表，只能用数据泵方式，传统方式只能跳过

二、数据泵方式完整流程

1.创建数据库表空间及其dbf文件

CRATE TABLESPACE "DB_USER" DATAFILE '/home/oracle/user_db.dbf' SIZE 209710 AUTOEXTEND ON NEXT 8192 MAXSIZE 31231314M LOGGING ONLINE PERMANTET BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT MANAGEMENT AUTO;

2.表空间dbf文件扩容

ALTER DATABASE DATAFILE '/home/oracle/userDB.dbf' RESIZE 8160608256; --文件扩容
ALTER DATABASE DATAFILE '/home/oracle/userDB.dbf' autoentend on maxsize unlimited; --不限制大小

3.创建数据库用户并指定表空间

create user DB_USER IDENTIFIED BY "Password" default tablespace DB_USER temporary tablespace TEMP；

4.为用户授权

grant read,write on directory SYS.DMPFILE to DB_USER;
grant connect to DB_USER;
grant resource to DB_USER;
grant create database link to DB_USER;
grant create table to DB_USER;
grant create view to DB_USER;

5.创建逻辑目录并授权

create or replace directory DUMP_DIR as '/home/online/oracle_file'; --创建逻辑目录
grant read,write on directory DUMP_DIR to DB_USER;  --授予用户逻辑目录操作权限

6.表空间维度导出数据库内容

expdp DB_USER/online @10.1.1.103:1521/orcl DIRECTORY=DUMP_DIR  DUMPFILE=db_user.dmp  

7.将dmp文件内容导入当前数据库

impdp DB_USER/online @10.1.1.103:1521/orcl DIRECTORY=DUMP_DIR  DUMPFILE=db_user.dmp REMAP_TABLESPACE=USER:DB_USER REMAP_SCHEMA=USER:DB_USER TABLE_EXISTS_ACTION=REPLACE

三、注意事项

• 使用数据泵方式进行表空间维度导入时，要注意原表空间和当前表空间是否一致，如果不一致导入会报错

• 导入时要判断当前表空间的dbf容量是否足够，如果空间不够需要进行dbf文件扩容后再进行导入

• 数据泵方式如果数据库的密码中有@字符，需要用’ ''password" ’ 将密码内容包起来,先用单引号,再用双引号

• 数据泵方式导出时要先确认逻辑目录剩余空间大小，以防空间不足导致导出失败，同时要确认导出的逻辑目录对应的路径，找到导出的dmp文件

四、误删dbf文件后报错处理

1.报错情景和内容

​ 如果直接删除数据库的dbf文件，如果oracle不重启就没有问题，如果oracle被杀掉再启动，oracle就会报错。Oracle在启动时要将方案中原有配置信息装载进入，装载过程中配置中有文件未找到，所以就报出错误;误删dbf文件之后,数据库不会有问题,但是如果数据库进行重启的时候会报错，报错内容如下：

ORA-01157: 无法标识/锁定数据文件 5 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 5: ‘F:/ORACLE/TEST01.DBF’  --说明是标号为5的dbf文件被删除,需要先恢复再脱机

2.问题解决

• SQL>shutdown normal ； --关闭数据库连接

• SQL>startup mount;

• SQL>alter database open; --打开数据库查看有哪些文件已经删除，但是在数据库控制文件中还存在记’’

  如果误删了dbf文件,会出现以下报错:
  ORA-01157: 无法标识/锁定数据文件 5 - 请参阅 DBWR 跟踪文件
  ORA-01110: 数据文件 5: ‘F:/ORACLE/TEST01.DBF’
  说明是标号为5的dbf文件被删除,需要先恢复再脱机

• SQL> alter database create datafile 5

• SQL> alter database datafile 5 offline drop;

• 执行完以上步骤后在进行重启,如果还报错,说明删除的文件不止一个,重复上面的操作直到不报错问题就解决了

3.删除表空间和dbf文件的正确命令

DROP TABLESPACE DB_USER INCLUDING CONTENTS AND DATAFILES;